﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using MySql.Data.MySqlClient;
using System.Data.SqlClient;

namespace Pulog.DataTransfer
{
    public partial class Form1 : Form
    {

        #region variables

        private MySqlConnection conn;
        private DataTable data;
        private MySqlDataAdapter da;

        #endregion
        public Form1()
        {
            InitializeComponent();
        }



        private void PerformBulkCopyXMLDataSource()
        {
            if (conn != null)
                conn.Close();
            string mysqlconnectionString = String.Format("server={0};user id={1}; password={2}; pooling=false",
                txtHost.Text, txtUsername.Text, txtPassword.Text);
            try
            {
                conn = new MySqlConnection(mysqlconnectionString);
                conn.Open();

                GetDatabases();
            }
            catch (MySqlException ex)
            {
                MessageBox.Show("Error connecting to the server: " + ex.Message);
            }

            
        }

        private void GetDatabases()
        {
            MySqlDataReader reader = null;
            MySqlCommand cmd = new MySqlCommand("SHOW DATABASES", conn);
            try
            {
                reader = cmd.ExecuteReader();
                CbxDatabaseList.Items.Clear();
                while (reader.Read())
                {
                    CbxDatabaseList.Items.Add(reader.GetString(0));
                }
            }
            catch (MySqlException ex)
            {
                MessageBox.Show("Failed to populate database list: " + ex.Message);
            }
            finally
            {
                if (reader != null) reader.Close();
            }
        }

        private void button1_Click(object sender, EventArgs e)
        {
            PerformBulkCopyXMLDataSource();
        }

        private void CbxDatabaseList_SelectedIndexChanged(object sender, EventArgs e)
        {
            MySqlDataReader reader = null;

            conn.ChangeDatabase(CbxDatabaseList.SelectedItem.ToString());

            MySqlCommand cmd = new MySqlCommand("SHOW TABLES", conn);
            try
            {
                reader = cmd.ExecuteReader();
                CbxTableList.Items.Clear();
                while (reader.Read())
                {
                    CbxTableList.Items.Add(reader.GetString(0));
                }
            }
            catch (MySqlException ex)
            {
                MessageBox.Show("Failed to populate table list: " + ex.Message);
            }
            finally
            {
                if (reader != null) reader.Close();
            }
        }

        private void CbxTableList_SelectedIndexChanged(object sender, EventArgs e)
        {
            data = new DataTable();

            da = new MySqlDataAdapter("SELECT * FROM " + CbxTableList.SelectedItem.ToString(), conn);
            da.Fill(data);

            //DataSet ds = new DataSet();
            //DataTable sourceData = new DataTable();
            //ds.ReadXml(@"C:\Products.xml");
            //data = ds.Tables[0];
            
            
            // open the destination data
            try
            {
                //using (SqlConnection destinationConnection = new SqlConnection(@"Server=localhost;Database=D:\SHAWN'S PROJECT\PULOG\SRC\PULOG\PULOG.WEB\APP_DATA\PULOG.MDF;User ID=sa;Password=1003never;Trusted_Connection=True;"))
                using (SqlConnection destinationConnection = new SqlConnection(@"Server=.\sql2005;Database=pulog;Integrated Security=SSPI;Trusted_Connection=True;"))
                {
                    // open the connection

                    destinationConnection.Open();
                    using (SqlBulkCopy bulkCopy = new SqlBulkCopy(destinationConnection.ConnectionString))
                    {
                        // column mappings
                        if (CbxTableList.SelectedItem.ToString() == "boblog_blogs")
                        {
                            bulkCopy.ColumnMappings.Add("blogid", "postID");
                            bulkCopy.ColumnMappings.Add("title", "title");
                            bulkCopy.ColumnMappings.Add("content", "content");
                            bulkCopy.ColumnMappings.Add("pubtime", "pubTime");
                            bulkCopy.ColumnMappings.Add("authorid", "authorID");
                            bulkCopy.ColumnMappings.Add("category", "categoryID");
                            bulkCopy.ColumnMappings.Add("views", "views");
                            bulkCopy.ColumnMappings.Add("sticky", "sticky");
                            bulkCopy.ColumnMappings.Add("title", "slug");
                            bulkCopy.ColumnMappings.Add("property", "property");
                            bulkCopy.ColumnMappings.Add("blogid", "password");
                            bulkCopy.DestinationTableName = "dbo.Post";
                        }
                        else if(CbxTableList.SelectedItem.ToString() == "boblog_user")
                        {
                            bulkCopy.ColumnMappings.Add("userid", "userID");
                            bulkCopy.ColumnMappings.Add("username", "userName");
                            bulkCopy.ColumnMappings.Add("userpsw", "password");
                            bulkCopy.DestinationTableName = "dbo.[User]";
                            
                        }
                        else if (CbxTableList.SelectedItem.ToString() == "boblog_categories")
                        {
                            bulkCopy.ColumnMappings.Add("cateid", "categoryID");
                            bulkCopy.ColumnMappings.Add("catename", "categoryName");
                            bulkCopy.ColumnMappings.Add("catedesc", "categoryDesc");
                            bulkCopy.ColumnMappings.Add("cateorder", "categoryOrder");
                            bulkCopy.DestinationTableName = "dbo.Category";
                        }
                        else if (CbxTableList.SelectedItem.ToString() == "boblog_replies")
                        {
                            bulkCopy.ColumnMappings.Add("repid", "CommentID");
                            bulkCopy.ColumnMappings.Add("blogid", "PostID");
                            bulkCopy.ColumnMappings.Add("reptime", "CommentTime");
                            bulkCopy.ColumnMappings.Add("replier", "AuthorName");
                            bulkCopy.ColumnMappings.Add("repemail", "AuthorEmail");
                            bulkCopy.ColumnMappings.Add("repurl", "AuthorURL");
                            bulkCopy.ColumnMappings.Add("repip", "AuthorIP");
                            bulkCopy.ColumnMappings.Add("repcontent", "CommentContent");
                            bulkCopy.ColumnMappings.Add("reproperty", "CommentProperty");
                            bulkCopy.DestinationTableName = "dbo.Comment";
                        }
                        else if (CbxTableList.SelectedItem.ToString() == "boblog_replies_parent")
                        {
                            bulkCopy.ColumnMappings.Add("repid", "CommentID");
                            bulkCopy.ColumnMappings.Add("blogid", "PostID");
                            bulkCopy.ColumnMappings.Add("ParentID", "CommentParentID");
                            bulkCopy.ColumnMappings.Add("adminreplier", "AuthorName");
                            bulkCopy.ColumnMappings.Add("adminrepcontent", "CommentContent");
                            bulkCopy.ColumnMappings.Add("reproperty", "CommentProperty");
                            bulkCopy.DestinationTableName = "dbo.Comment";
                        }

                        else if (CbxTableList.SelectedItem.ToString() == "boblog_upload")
                        {
                            bulkCopy.ColumnMappings.Add("fid", "attachmentID");
                            bulkCopy.ColumnMappings.Add("filepath", "filePath");
                            bulkCopy.ColumnMappings.Add("originalname", "originalName");
                            bulkCopy.ColumnMappings.Add("dltime", "downloadCount");
                            bulkCopy.ColumnMappings.Add("uploadtime", "uploadTime");
                            bulkCopy.ColumnMappings.Add("uploaduser", "uploadUserID");
                            bulkCopy.DestinationTableName = "dbo.Attachment";
                        }
                        else if (CbxTableList.SelectedItem.ToString() == "boblog_links")
                        {
                            bulkCopy.ColumnMappings.Add("linkid", "linkID");
                            bulkCopy.ColumnMappings.Add("linkname", "linkName");
                            bulkCopy.ColumnMappings.Add("linkdesc", "linkDescription");
                            bulkCopy.ColumnMappings.Add("linkurl", "linkURL");
                            bulkCopy.ColumnMappings.Add("linkorder", "linkOrder");
                            bulkCopy.DestinationTableName = "dbo.Links";
                        }
                        else if (CbxTableList.SelectedItem.ToString() == "boblog_counter")
                        {

                        }
                        bulkCopy.WriteToServer(data);

                    }
                }
                MessageBox.Show("Transfer successed.");
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }


    }
}
